# -*- coding: UTF-8 -*-
# Project : zhaobiao
# File : toMysql.py
# IDE : PyCharm
# Author : 博科（鑫贝西）田聪
# Date : 2021/11/13 15:11

import pymysql

from datetime import datetime

# from settings import *
from new_tools.settings import *


class MySqlLink:

    def __init__(self):
        self.config = {
            "host": HOST,
            "user": USER,
            "password": PASSWORD,
            "database": "daohang",
            'cursorclass': pymysql.cursors.DictCursor
        }
        self.db = pymysql.connect(**self.config)
        self.cursor = self.db.cursor()
        self.new_timestamp = datetime.now()  #

    def __del__(self):
        try:
            self.db.ping()
            self.cursor.close()
            self.db.close()
            print("数据库已关闭")
        except:
            print("数据库已自己关闭")

    def get_search_key(self, surface):
        """
        查询关键字，并按优先级排序
        :param surface: 表名
        :return:
        """
        sql_txt = f'SELECT * FROM `{surface}`'
        self.cursor.execute(sql_txt)
        slv = self.cursor.fetchall()
        slv.sort(key=lambda k: (k.get('level', 0)))
        return [i.get('search_key') for i in slv]

    def item_update(self, items, surface='fa_successful_tenderer'):
        flag = 0
        for item in items:
            sql_txt = f"UPDATE {surface} SET legal_person='{item['legal_person']}',phone='{item['phone']}',province='{item['province']}',address='{item['address']}' WHERE bid_winner='{item['bid_winner']}';"
            self.cursor.execute(sql_txt)
            flag += 1
            if flag == 1000:
                print("录入成功")
                self.db.commit()
                flag = 0

        if flag < 1000:
            print("录入成功")
            self.db.commit()

    def insert_item(self, item):
        """
        执行sql写入修改语句，
        :param sql_sentence: sql语句，可以是写入可以是更改
        :param ID: sha1 id
        :return:None
        """
        try:
            now_timestamp = datetime.now()
            delta_T = (now_timestamp - self.new_timestamp).seconds / 60  # 分钟
            # 两分钟判断一次
            if delta_T > 2:
                print("判断数据库链接是否存活")
                self.db.ping()
                self.new_timestamp = now_timestamp
        except:
            print("当前数据库链接已关闭，正在重新链接.......")
            self.db = pymysql.connect(**self.config)
            self.cursor = self.db.cursor()
            print("=-=-=-=-= 数据库已连接 -=-=-=-=-=")
        finally:
            sql_sen = f"INSERT INTO `fa_article`(id,search_key,title,content,createtime,url) VALUES ('{item['id']}','{item['search_key']}','{item['title']}','{item['content']}', '{item['createtime']}','{item['url']}')"
            try:
                self.cursor.execute(sql_sen)
                self.db.commit()
                print(f'######### {item["id"]} 写入成功 #####################')
            except:
                print(f'######### {item["id"]} 已存在 #####################')


if __name__ == '__main__':
    c = MySqlLink()
    print(c.get_search_key('fa_st_sk'))
